package makovo.data;

import java.io.IOException;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import com.itextpdf.text.pdf.PdfStructTreeController.returnType;

import makovo.data.Items.DocumentType;
import makovo.entities.Address;
import makovo.entities.Applicant;
import makovo.entities.Applicant.ApplicantStep;
import makovo.entities.ApplicantDocReport;
import makovo.entities.ApplicantWithDocs;
import makovo.entities.Document;
import makovo.entities.Project;
import makovo.entities.ProjectAddress;
import makovo.entities.SocInfo;
import makovo.entities.SysUser;
import makovo.entities.Validators;
import makovo.utils.DataAccess;
import makovo.utils.FTPAccess;

public class Resources {
	private static SimpleDateFormat mFormat = new SimpleDateFormat("yyyy-MM-dd");

	public static long getTotalApplicants() throws IOException, SQLException, Exception {
		long ret = (long) 0.0;

		ret = DataAccess.getInstance().<Long> execValue("sp_obtener_total_aplicantes()", null);

		return ret;
	}

	public static long getTotalApplicantsFilter(final String curp, final Integer step, final Integer state, final Integer city,
			final Integer loc) throws IOException, SQLException, Exception {
		long ret = (long) 0.0;

		ret = DataAccess.getInstance().<Long> execValue("sp_total_aplicantes_filtro(?,?,?,?,?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_curp", curp });
				add(new String[] { "p_step", step == null ? null : String.valueOf(step) });
				add(new String[] { "p_state_id", state == null ? null : String.valueOf(state) });
				add(new String[] { "p_city_id", city == null ? null : String.valueOf(city) });
				add(new String[] { "p_loc_id", loc == null ? null : String.valueOf(loc) });
			}
		});

		return ret;
	}

	public static long getTotalApreciaFilter(final String curp, final Integer step, final Integer state, final Integer city) throws IOException, SQLException, Exception {
		long ret = (long) 0.0;

		ret = DataAccess.getInstance().<Long> execValue("sp_total_aprecia_filtro(?,?,?,?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_curp", curp });
				add(new String[] { "p_step", step == null ? null : String.valueOf(step) });
				add(new String[] { "p_state_id", state == null ? null : String.valueOf(state) });
				add(new String[] { "p_city_id", city == null ? null : String.valueOf(city) });
			}
		});

		return ret;
	}

	public static ArrayList<ApplicantDocReport> getDocReport(final int startRow, final int pageSize) throws IOException,
			SQLException, Exception {
		ApplicantDocReport current = null;
		ArrayList<ApplicantDocReport> apps = new ArrayList<>();

		ResultSet rs = DataAccess.getInstance().execResult("sp_mostrar_documentos(?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_fecha_inicio", String.valueOf(startRow) });
				add(new String[] { "p_fecha_fin", String.valueOf(pageSize) });
			}
		});

		while (rs.next()) {
			if (current == null || !rs.getString(rs.findColumn("curp")).equals(current.curp)) {
				current = new ApplicantDocReport(rs.getString(rs.findColumn("curp")), rs.getString(rs.findColumn("nombre")),
						rs.getString(rs.findColumn("apellido_paterno")), rs.getString(rs.findColumn("apellido_materno")));

				apps.add(current);
			}

			current.addDoc(rs.getInt(rs.findColumn("id_tipo_documento")));
		}

		DataAccess.getInstance().close();

		return apps;
	}

	public static ArrayList<SysUser> getSystemUsers(final Date init, final Date end) throws Exception {
		ArrayList<SysUser> users = new ArrayList<>();
		SysUser current;

		ResultSet rs = DataAccess.getInstance().execResult("sp_obtener_usuarios(?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_fecha_inicio", mFormat.format(init) });
				add(new String[] { "p_fecha_fin", mFormat.format(end) });

			}
		});

		while (rs.next()) {
			current = new SysUser(rs.getString(rs.findColumn("email")), rs.getString(rs.findColumn("nombre")), rs.getString(rs
					.findColumn("apellido_pat")), rs.getString(rs.findColumn("apellido_mat")),
					rs.getInt(rs.findColumn("estado")), rs.getString(rs.findColumn("fecha_afiliacion")), rs.getInt(rs
							.findColumn("id_tipo_usuario")), rs.getString(rs.findColumn("tipo_usuario")));

			users.add(current);
		}

		DataAccess.getInstance().close();

		return users;
	}

	public static boolean changePassword(final String email, final String pass) throws Exception {
		boolean ret = true;
		DataAccess.getInstance().exec("changePassword(?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "inemail", String.valueOf(email) });
				add(new String[] { "inpass", pass });
			}
		});

		return ret;
	}

	public static boolean deleteApplicant(final String curp, final String email) throws Exception {
		boolean ret = false;
		DataAccess.getInstance().exec("sp_delete_Applicant(?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "dCurp", curp.toString() });
				add(new String[] { "nEmail", email.toString() });
			}
		});
		ret = true;

		return ret;
	}

	public static boolean saveReferences(final int addressID, final String streetOne, final String streetTwo, final int type)
			throws Exception {
		boolean ret = true;

		DataAccess.getInstance().exec("sp_modificar_referencias (?,?,?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_id_domicilio", String.valueOf(addressID) });
				add(new String[] { "p_calle_uno", streetOne });
				add(new String[] { "p_calle_dos", streetTwo });
				add(new String[] { "p_tipo_vialidad", String.valueOf(type) });
			}
		});

		return ret;
	}

	public static int saveAddress(final String state, final int stateId, final String city, final int cityId, final String loc,
			final int locId, final String cp, final String street, final String extNum, final String intNum, final double lonng,
			final double lat, final String col) throws Exception {
		int ret = -1;

		ret = DataAccess.getInstance().<Integer> execValue("sp_guardar_domicilio(?,?,?,?,?,?,?,?,?,?,?,?,?)",
				new ArrayList<String[]>() {
					private static final long serialVersionUID = 1L;

					{
						add(new String[] { "p_estado", state });
						add(new String[] { "p_clave_estado", String.valueOf(stateId) });
						add(new String[] { "p_municipio", city });
						add(new String[] { "p_clave_municipio", String.valueOf(cityId) });
						add(new String[] { "p_localidad", loc });
						add(new String[] { "p_clave_localidad", String.valueOf(locId) });
						add(new String[] { "p_codigo_postal", cp });
						add(new String[] { "p_calle", street });
						add(new String[] { "p_numero_externo", extNum });
						add(new String[] { "p_numero_interno", intNum });
						add(new String[] { "p_longitud", String.valueOf(lonng) });
						add(new String[] { "p_latitud", String.valueOf(lat) });
						add(new String[] { "p_colonia", String.valueOf(col) });
					}
				});

		return ret;
	}

	public static boolean saveApplicantProject(final String curp, final String projId, final int projTypeId,
			final int projSettType, final int addressId) throws Exception {
		boolean success = true;

		DataAccess.getInstance().exec("sp_modificar_info_proyecto(?,?,?,?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_curp", curp });
				add(new String[] { "p_id_domicilio", String.valueOf(addressId) });
				add(new String[] { "p_folio", projId });
				add(new String[] { "p_id_tipo_proyecto", String.valueOf(projTypeId) });
				add(new String[] { "p_id_tipo_asentamiento", String.valueOf(projSettType) });
			}
		});

		return success;
	}

	public static boolean saveApplicantSocInfo(final String curp, final int addressId, final int ant, final int dependants,
			final int jobTypeId, final String ent, final double earn, final String job) throws Exception {
		boolean success = true;

		DataAccess.getInstance().exec("sp_modificar_info_socioeconomica(?,?,?,?,?,?,?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_curp", curp });
				add(new String[] { "p_id_domicilio", String.valueOf(addressId) });
				add(new String[] { "p_antiguedad", String.valueOf(ant) });
				add(new String[] { "p_dependientes", String.valueOf(dependants) });
				add(new String[] { "p_id_tipo_trabajo", String.valueOf(jobTypeId) });
				add(new String[] { "p_empresa", ent });
				add(new String[] { "p_ingreso", String.valueOf(earn) });
				add(new String[] { "p_puesto", job });
			}
		});

		return success;
	}

	public static boolean changeApplicantStep(final String curp, final ApplicantStep step) throws Exception {
		return changeApplicantStep(curp, step, null, null);
	}

	public static boolean changeApplicantStep(final String curp, final ApplicantStep step, final ApplicantStep apreciaStep,
			final String comments) throws Exception {
		boolean success = true;

		DataAccess.getInstance().exec("sp_cambiar_paso_aplicante(?,?,?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_curp", curp });
				add(new String[] { "p_paso", step == null ? null : String.valueOf(step.id) });
				add(new String[] { "p_paso_aprecia", apreciaStep == null ? null : String.valueOf(apreciaStep.id) });
				add(new String[] { "p_comentarios", comments == null ? null : comments });
			}
		});

		return success;
	}

	public static ApplicantWithDocs getApplicantWithDocs(final String curp) throws Exception {
		ApplicantWithDocs a = new ApplicantWithDocs(getApplicantInfoByCURP(curp));
		Document currentDoc;
		String temp;

		ResultSet rs;

		rs = DataAccess.getInstance().execResult("sp_obtener_documentos_aplicante(?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_curp", curp });
			}
		});

		while (rs.next()) {
			temp = rs.getString(rs.findColumn("ruta"));

			if (temp.contains("/public_html"))
				temp = temp.replace("/public_html", "");

			currentDoc = new Document("http://" + FTPAccess.getInstance().getServer() + temp,
					rs.getString(rs.findColumn("curp")),
					Items.DocumentType.valueOf(rs.getInt(rs.findColumn("id_tipo_documento"))));

			a.addDoc(currentDoc);
		}

		DataAccess.getInstance().close();

		return a;
	}

	public static ApplicantWithDocs getApplicantWithDocs_ftp(final String curp) throws Exception {
		ApplicantWithDocs a = new ApplicantWithDocs(getApplicantInfoByCURP(curp));
		Document currentDoc;
		String temp;

		ResultSet rs;

		rs = DataAccess.getInstance().execResult("sp_obtener_documentos_aplicante(?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_curp", curp });
			}
		});

		while (rs.next()) {
			temp = rs.getString(rs.findColumn("ruta"));
			temp = temp.replaceFirst("/public_html/Aplicantes/", "");
			currentDoc = new Document(temp, rs.getString(rs.findColumn("curp")), Items.DocumentType.valueOf(rs.getInt(rs
					.findColumn("id_tipo_documento"))));

			a.addDoc(currentDoc);
		}

		DataAccess.getInstance().close();

		return a;
	}

	public static SysUser getUserByMail(final String email) throws Exception {
		SysUser current = null;
		ResultSet rs;

		rs = DataAccess.getInstance().execResult("sp_obtener_usuario(?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_email", email });
			}
		});

		if (rs.next()) {
			current = new SysUser(rs.getString(rs.findColumn("email")), rs.getString(rs.findColumn("nombre")), rs.getString(rs
					.findColumn("apellido_pat")), rs.getString(rs.findColumn("apellido_mat")),
					rs.getInt(rs.findColumn("estado")), rs.getInt(rs.findColumn("id_ciudad")), rs.getInt(rs
							.findColumn("id_localidad")), rs.getString(rs.findColumn("fecha_afiliacion")), rs.getInt(rs
							.findColumn("id_tipo_usuario")), rs.getString(rs.findColumn("id_tipo_usuario")));

		}

		DataAccess.getInstance().close();

		return current;
	}

	public static Applicant getApplicantInfoByCURP(final String curp) throws Exception {
		Applicant ap = null;
		Address current;
		ResultSet rs;

		rs = DataAccess.getInstance().execResult("sp_obtener_informacion_aplicante(?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_curp", curp });
			}
		});

		if (rs.next()) {
			current = new Address(rs.getInt(rs.findColumn("id_domicilio")), rs.getString(rs.findColumn("estado")), rs.getInt(rs
					.findColumn("clave_estado")), rs.getString(rs.findColumn("municipio")), rs.getInt(rs
					.findColumn("clave_municipio")), rs.getString(rs.findColumn("localidad")), rs.getInt(rs
					.findColumn("clave_localidad")), rs.getString(rs.findColumn("codigo_postal")), rs.getString(rs
					.findColumn("calle")), rs.getString(rs.findColumn("numero_externo")), rs.getString(rs
					.findColumn("numero_interno")), rs.getString(rs.findColumn("longitud")), rs.getString(rs
					.findColumn("latitud")), rs.getString(rs.findColumn("colonia")));

			ap = new Applicant(rs.getString(rs.findColumn("curp")), rs.getString(rs.findColumn("nombre")), rs.getString(rs
					.findColumn("apellido_paterno")), rs.getString(rs.findColumn("apellido_materno")), rs.getString(rs
					.findColumn("telefono")), rs.getString(rs.findColumn("folio")), rs.getString(rs.findColumn("folio_id")),
					rs.getString(rs.findColumn("fecha_nacimiento")), rs.getInt(rs.findColumn("id_tipo_identificacion")),
					rs.getInt(rs.findColumn("id_estado_civil")), rs.getString(rs.findColumn("tipo_identificacion")),
					rs.getString(rs.findColumn("estado_civil")), rs.getInt(rs.findColumn("id_paso")), rs.getInt(rs
							.findColumn("id_paso_aprecia")), current);
		}

		DataAccess.getInstance().close();

		return ap;
	}

	public static SocInfo getSocInfoByCURP(final String curp) throws Exception {
		SocInfo sInfo = null;
		Address current;
		ResultSet rs;

		rs = DataAccess.getInstance().execResult("sp_obtener_info_socioeconomica(?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_curp", curp });
			}
		});

		if (rs.next()) {
			current = new Address(rs.getInt(rs.findColumn("id_domicilio")), rs.getString(rs.findColumn("estado")), rs.getInt(rs
					.findColumn("clave_estado")), rs.getString(rs.findColumn("municipio")), rs.getInt(rs
					.findColumn("clave_municipio")), rs.getString(rs.findColumn("localidad")), rs.getInt(rs
					.findColumn("clave_localidad")), rs.getString(rs.findColumn("codigo_postal")), rs.getString(rs
					.findColumn("calle")), rs.getString(rs.findColumn("numero_externo")), rs.getString(rs
					.findColumn("numero_interno")), rs.getString(rs.findColumn("longitud")), rs.getString(rs
					.findColumn("latitud")), rs.getString(rs.findColumn("colonia")));

			sInfo = new SocInfo(rs.getString(rs.findColumn("curp")), rs.getString(rs.findColumn("antiguedad")), rs.getString(rs
					.findColumn("dependientes")), rs.getString(rs.findColumn("empresa")), rs.getString(rs
					.findColumn("ingreso_mensual")), rs.getString(rs.findColumn("puesto")), rs.getString(rs
					.findColumn("tipo_trabajo")), rs.getInt(rs.findColumn("id_tipo_trabajo")), current);
		}

		DataAccess.getInstance().close();

		return sInfo;
	}

	public static ResultSet getInfoExport(final String estado, final String municipio) throws Exception
	{
		ResultSet rs = null;
		rs = DataAccess.getInstance().execResult("sp_exportar_aplicantes(?,?)", new ArrayList<String[]>() {
			{
				add(new String[] { "idEstado", estado });
				add(new String[] { "idMunicipio", municipio});
			}
		});

		//DataAccess.getInstance().close();
		return rs;
	}
	
	public static Project getProjectInfoByCURP(final String curp) throws Exception {
		Project sInfo = null;
		ProjectAddress current;
		ResultSet rs;

		rs = DataAccess.getInstance().execResult("sp_obtener_proyecto(?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_curp", curp });
			}
		});

		if (rs.next()) {
			current = new ProjectAddress(rs.getInt(rs.findColumn("id_domicilio")), rs.getString(rs.findColumn("estado")),
					rs.getInt(rs.findColumn("clave_estado")), rs.getString(rs.findColumn("municipio")), rs.getInt(rs
							.findColumn("clave_municipio")), rs.getString(rs.findColumn("localidad")), rs.getInt(rs
							.findColumn("clave_localidad")), rs.getString(rs.findColumn("codigo_postal")), rs.getString(rs
							.findColumn("calle")), rs.getString(rs.findColumn("numero_externo")), rs.getString(rs
							.findColumn("numero_interno")), rs.getString(rs.findColumn("longitud")), rs.getString(rs
							.findColumn("latitud")), rs.getInt(rs.findColumn("idReferenciasDomicilio")), rs.getString(rs
							.findColumn("entre_calle_uno")), rs.getString(rs.findColumn("entre_Calle_dos")), rs.getInt(rs
							.findColumn("tipoVialidad")), rs.getString(rs.findColumn("colonia")));

			sInfo = new Project(rs.getString(rs.findColumn("folio")), rs.getString(rs.findColumn("curp")), rs.getString(rs
					.findColumn("tipo_proyecto")), rs.getInt(rs.findColumn("id_tipo_proyecto")), rs.getString(rs
					.findColumn("tipo_asentamiento")), rs.getInt(rs.findColumn("id_tipo_asentamiento")), current);
		}

		DataAccess.getInstance().close();

		return sInfo;
	}

	public static ArrayList<Applicant> getApreciaApplicants(final String curp, final Integer state, final Integer city,
			final Integer step, final int page, final int pageSize) throws Exception {
		ArrayList<Applicant> applicants = new ArrayList<Applicant>();
		Address current;

		ResultSet rs = DataAccess.getInstance().execResult("sp_obtener_aplicantes_aprecia(?,?,?,?,?,?)",
				new ArrayList<String[]>() {
					{
						add(new String[] { "p_curp", curp });
						add(new String[] { "p_state_id", state == null ? null : String.valueOf(state) });
						add(new String[] { "p_city_id", city == null ? null : String.valueOf(city) });
						add(new String[] { "p_step", step == null ? null : String.valueOf(step) });
						add(new String[] { "p_page", String.valueOf(page) });
						add(new String[] { "p_pagesize", String.valueOf(pageSize) });
					}
				});

		while (rs.next()) {
			current = new Address(rs.getInt(rs.findColumn("id_domicilio")), rs.getString(rs.findColumn("estado")), rs.getInt(rs
					.findColumn("clave_estado")), rs.getString(rs.findColumn("municipio")), rs.getInt(rs
					.findColumn("clave_municipio")), rs.getString(rs.findColumn("localidad")), rs.getInt(rs
					.findColumn("clave_localidad")), rs.getString(rs.findColumn("codigo_postal")), rs.getString(rs
					.findColumn("calle")), rs.getString(rs.findColumn("numero_externo")), rs.getString(rs
					.findColumn("numero_interno")), rs.getString(rs.findColumn("longitud")), rs.getString(rs
					.findColumn("latitud")), rs.getString(rs.findColumn("colonia")));

			applicants.add(new Applicant(rs.getString(rs.findColumn("curp")), rs.getString(rs.findColumn("nombre")), rs
					.getString(rs.findColumn("apellido_paterno")), rs.getString(rs.findColumn("apellido_materno")), rs
					.getString(rs.findColumn("telefono")), rs.getString(rs.findColumn("folio")), rs.getString(rs
					.findColumn("folio_id")), rs.getString(rs.findColumn("fecha_nacimiento")), rs.getInt(rs
					.findColumn("id_tipo_identificacion")), rs.getInt(rs.findColumn("id_estado_civil")), rs.getString(rs
					.findColumn("tipo_identificacion")), rs.getString(rs.findColumn("estado_civil")), rs.getInt(rs
					.findColumn("id_paso")), rs.getInt(rs.findColumn("id_paso_aprecia")), current));
		}

		DataAccess.getInstance().close();

		return applicants;
	}

	public static ArrayList<Applicant> getApplicants(final String curp, final Integer step, final Integer state,
			final Integer city, final Integer loc, final int page, final int pageSize) throws Exception {
		ArrayList<Applicant> applicants = new ArrayList<Applicant>();
		Address current;

		ResultSet rs = DataAccess.getInstance().execResult("sp_obtener_aplicantes(?,?,?,?,?,?,?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_curp", curp });
				add(new String[] { "p_step", step == null ? null : String.valueOf(step) });
				add(new String[] { "p_state_id", state == null ? null : String.valueOf(state) });
				add(new String[] { "p_city_id", city == null ? null : String.valueOf(city) });
				add(new String[] { "p_loc_id", loc == null ? null : String.valueOf(loc) });
				add(new String[] { "p_page", String.valueOf(page) });
				add(new String[] { "p_pagesize", String.valueOf(pageSize) });
			}
		});

		while (rs.next()) {
			current = new Address(rs.getInt(rs.findColumn("id_domicilio")), rs.getString(rs.findColumn("estado")), rs.getInt(rs
					.findColumn("clave_estado")), rs.getString(rs.findColumn("municipio")), rs.getInt(rs
					.findColumn("clave_municipio")), rs.getString(rs.findColumn("localidad")), rs.getInt(rs
					.findColumn("clave_localidad")), rs.getString(rs.findColumn("codigo_postal")), rs.getString(rs
					.findColumn("calle")), rs.getString(rs.findColumn("numero_externo")), rs.getString(rs
					.findColumn("numero_interno")), rs.getString(rs.findColumn("longitud")), rs.getString(rs
					.findColumn("latitud")), rs.getString(rs.findColumn("colonia")));

			applicants.add(new Applicant(rs.getString(rs.findColumn("curp")), rs.getString(rs.findColumn("nombre")), rs
					.getString(rs.findColumn("apellido_paterno")), rs.getString(rs.findColumn("apellido_materno")), rs
					.getString(rs.findColumn("telefono")), rs.getString(rs.findColumn("folio")), rs.getString(rs
					.findColumn("folio_id")), rs.getString(rs.findColumn("fecha_nacimiento")), rs.getInt(rs
					.findColumn("id_tipo_identificacion")), rs.getInt(rs.findColumn("id_estado_civil")), rs.getString(rs
					.findColumn("tipo_identificacion")), rs.getString(rs.findColumn("estado_civil")), rs.getInt(rs
					.findColumn("id_paso")), rs.getInt(rs.findColumn("id_paso_aprecia")), current));
		}

		DataAccess.getInstance().close();

		return applicants;
	}

	public static boolean saveFilePath(final String curp, final String path, final DocumentType docType) throws Exception {
		boolean success = false;

		DataAccess.getInstance().exec("sp_guardar_archivo(?,?,?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_curp", curp });
				add(new String[] { "p_ruta", path });
				add(new String[] { "p_id_tipo_documento", String.valueOf(docType.id) });
			}
		});

		return success;
	}

	public static boolean saveApplicantInformation(final String CURP, final String nombre, final String ap_paterno,
			final String ap_materno, final String fechaNac, final String typeID, final String folioID, final String estadoCivil,
			final String telefono, final int idDomicilio, final int claveEstado, final String email) throws Exception {
		boolean success = false;

		DataAccess.getInstance().exec("sp_insert_applicant (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?,?)", new ArrayList<String[]>() {
			{
				add(new String[] { "nCURP", CURP });
				add(new String[] { "nNOMBRE", nombre });
				add(new String[] { "nAPELLIDO_PATERNO", ap_paterno });
				add(new String[] { "nAPELLIDO_MATERNO", ap_materno });
				add(new String[] { "nFECHA_NACIMIENTO", fechaNac });
				add(new String[] { "nTIPO_IDENTIF", typeID });
				add(new String[] { "nFOLIOID", folioID });
				add(new String[] { "nESTADO_CIVIL", estadoCivil });
				add(new String[] { "nTELEFONO", telefono });
				add(new String[] { "nID_DOMICILIO", String.valueOf(idDomicilio) });
				add(new String[] { "CLV_ESTADO", String.valueOf(claveEstado) });
				add(new String[] { "nEmail", String.valueOf(email) });
			}
		});
		success = true;

		return success;
	}

	public static boolean modifyApplicantInformation(final String curp, final String name, final String lastName,
			final String secondLast, final String birth, final int IDType, final String folID, final int status,
			final String phone, final int addressID) throws Exception {
		boolean success = false;

		DataAccess.getInstance().exec("sp_modificar_aplicante (?,?,?,?,?,?,?,?,?,?,?)", new ArrayList<String[]>() {
			{
				add(new String[] { "p_curp", curp });
				add(new String[] { "p_folio", "1" });
				add(new String[] { "p_nombre", name });
				add(new String[] { "p_ap_pat", lastName });
				add(new String[] { "p_ap_mat", secondLast });
				add(new String[] { "p_fecha_nac", birth });
				add(new String[] { "p_tipo_id", String.valueOf(IDType) });
				add(new String[] { "p_folio_id", folID });
				add(new String[] { "p_id_edo_civil", String.valueOf(status) });
				add(new String[] { "p_tel", phone });
				add(new String[] { "p_id_domicilio", String.valueOf(addressID) });
			}
		});
		success = true;

		return success;
	}

	public static ArrayList<Validators> getValidators(final Date init, final Date end) throws Exception {
		ArrayList<Validators> users = new ArrayList<>();
		Validators current;

		ResultSet rs = DataAccess.getInstance().execResult("sp_obtener_validadores(?,?)", new ArrayList<String[]>() {
			private static final long serialVersionUID = 1L;

			{
				add(new String[] { "p_fecha_inicio", mFormat.format(init) });
				add(new String[] { "p_fecha_fin", mFormat.format(end) });

			}
		});

		while (rs.next()) {

			current = new Validators(rs.getString(rs.findColumn("nombre")), rs.getString(rs.findColumn("apellidoPat")),
					rs.getString(rs.findColumn("apellidoMat")), rs.getString(rs.findColumn("estado")), rs.getString(rs
							.findColumn("fechaCreacion")), rs.getString(rs.findColumn("telefono")));

			users.add(current);
		}

		DataAccess.getInstance().close();

		return users;
	}

}
